﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using Tms.Model;
using Util.Common;
using Util.DBUtility;

namespace Tms.DAL
{
    public partial class manager_log
    {
        private string table_name = GlobalCache.DatabasePrefix + "manager_log";

        //获取最大ID
        private int GetMaxId(OleDbConnection conn, OleDbTransaction trans)
        {
            string sql = "select top 1 id from " + table_name + " order by id desc";
            object obj = DbHelperOleDb.GetSingle(conn, trans, sql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        //是否存在该记录
        public bool Exists(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from " + table_name);
            sql.Append(" where id=" + id);
            
            return DbHelperOleDb.Exists(sql.ToString());

        }
        public bool Exists(string strWhere)    //20191216 lxj
        {
            //校验
            if (strWhere == null) strWhere = "";

            //开始
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 1 id from " + table_name);
            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }

            return DbHelperOleDb.Exists(sql.ToString());
        }

        //获取记录数
        public int GetCount(string strWhere)
        {
            //校验
            if (strWhere == null) strWhere = "";

            //开始
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(*) as H from " + table_name); //【todo】
            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }

            return Convert.ToInt32(DbHelperOleDb.GetSingle(sql.ToString()));
        }

        //增加一条记录，返回自增主键值
        public int Add(Model.manager_log model)
        {
            if (model.remark == null) model.remark = "";  //20200414

            int newId = -1;

            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder sql = new StringBuilder();
                        sql.Append("insert into " + table_name + "(");
                        sql.Append("user_id,user_name,action_type,[remark],user_ip,add_time)");
                        sql.Append(" values (");
                        sql.Append("@user_id,@user_name,@action_type,@remark,@user_ip,@add_time)");

                        OleDbParameter[] parameters = {
                                new OleDbParameter("@user_id", OleDbType.Integer,4),
                                new OleDbParameter("@user_name", OleDbType.VarChar,255),
                                new OleDbParameter("@action_type", OleDbType.VarChar,255),
                                new OleDbParameter("@remark", OleDbType.VarChar,255),
                                new OleDbParameter("@user_ip", OleDbType.VarChar,255),
                                new OleDbParameter("@add_time", OleDbType.Date)};

                        parameters[0].Value = model.user_id;
                        parameters[1].Value = model.user_name;
                        parameters[2].Value = model.action_type;
                        parameters[3].Value = model.remark;
                        parameters[4].Value = model.user_ip;
                        parameters[5].Value = model.add_time;

                        DbHelperOleDb.ExecuteSql(conn, trans, sql.ToString(), parameters);

                        //取得新插入的ID
                        newId = GetMaxId(conn, trans);

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }

            return newId;
        }

        //将一行数据转换为实体对象
        public Model.manager_log DataRowToModel(DataRow row)
        {
            Model.manager_log model = new Model.manager_log();

            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["user_id"] != null && row["user_id"].ToString() != "")
                {
                    model.user_id = int.Parse(row["user_id"].ToString());
                }
                if (row["user_name"] != null)
                {
                    model.user_name = row["user_name"].ToString();
                }
                if (row["action_type"] != null)
                {
                    model.action_type = row["action_type"].ToString();
                }
                if (row["remark"] != null)
                {
                    model.remark = row["remark"].ToString();
                }
                if (row["user_ip"] != null)
                {
                    model.user_ip = row["user_ip"].ToString();
                }
                if (row["add_time"] != null && row["add_time"].ToString() != "")
                {
                    model.add_time = DateTime.Parse(row["add_time"].ToString());
                }
            }
            return model;
        }

        //获取一个实体对象
        public Model.manager_log GetModel(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 1 id,user_id,user_name,action_type,[remark],user_ip,add_time");
            sql.Append(" from " + table_name);
            sql.Append(" where id=@id");
            OleDbParameter[] parameters = { new OleDbParameter("@id", OleDbType.Integer,4)};
            parameters[0].Value = id;
                        
            DataSet ds = DbHelperOleDb.Query(sql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        //获取前几行数据
        public DataSet GetList(int topNum, string strWhere, string strOrder)
        {
            //校验
            if (strWhere == null) strWhere = "";
            if (strOrder == null) strOrder = "";

            //开始
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            if (topNum > 0)
            {
                sql.Append(" top " + topNum.ToString());
            }
            sql.Append(" id,user_id,user_name,action_type,[remark],user_ip,add_time");
            sql.Append(" from " + table_name);

            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }
            if (strOrder.Trim() != "")
            {
                sql.Append(" order by " + strOrder);
            }

            return DbHelperOleDb.Query(sql.ToString());
        }

        //获取查询分页数据
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string strOrder, out int recordCount)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + table_name);
            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }

            recordCount = Convert.ToInt32(DbHelperOleDb.GetSingle(PagingHelper.CreateCountingSql(sql.ToString())));
            return DbHelperOleDb.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, sql.ToString(), strOrder));
        }

        //根据用户名返回上一次登录记录
        public Model.manager_log GetModel(string user_name, int top_num, string action_type)
        {
            int rows = GetCount("user_name='" + user_name.Replace("'", "''") + "'");  //20200609
            if (top_num == 1)
            {
                rows = 2;
            }
            if (rows > 1)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select top 1 id from (select top " + top_num + " id from " + table_name);
                sql.Append(" where user_name='" + user_name.Replace("'", "''") + "'");  //20200609
                sql.Append(" and action_type='" + action_type + "'");
                sql.Append(" order by id desc) as T ");
                sql.Append(" order by id asc");

                object obj = DbHelperOleDb.GetSingle(sql.ToString());
                if (obj != null)
                {
                    return GetModel(Convert.ToInt32(obj));
                }
            }
            return null;
        }

        //删除7天前的日志数据
        public int Delete(int dayCount)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + table_name);
            sql.Append(" where DATEDIFF('d', add_time, date()) > " + dayCount);

            return DbHelperOleDb.ExecuteSql(sql.ToString());
        }

        
    }
}